package com.aaa.book_mall.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

/**
 * fileName:JDBCUtil
 * description:
 * author:付亚星
 * createTime:2020/12/30 19:08
 * version:1.0.0
 */
public class JDBCUtil {

    private static DataSource dataSource = null;

    static {
        try {
            Properties p = new Properties();
            InputStream stream = JDBCUtil.class.getClassLoader().getResourceAsStream("druid.properties");
            p.load(stream);
            //使用工厂模式
            dataSource = DruidDataSourceFactory.createDataSource(p);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 数据库连接
     */
    public static Connection getConn(){
        Connection conn = null;
        try {
           return dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 设置参数
     */
    public static void setParams(PreparedStatement psmt,Object...obj){
        //判断
        if(obj.length>0){
            for (int i = 0; i < obj.length; i++) {
                try {
                    psmt.setObject(i+1,obj[i]);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 查询
     */
    public static List<List> queryList(String sql,Object...obj){
        //连接
        Connection conn = getConn();
        PreparedStatement psmt = null;
        ResultSet rslt = null;
        ArrayList<List> maps = null;
        try {
            psmt = conn.prepareStatement(sql);
            //传参
            setParams(psmt,obj);
            //执行
            rslt = psmt.executeQuery();
            ResultSetMetaData met = rslt.getMetaData();
            maps = new ArrayList<>();
            while (rslt.next()){
                ArrayList<Object> list = new ArrayList<>();
                for (int i = 1; i <=met.getColumnCount() ; i++) {
                    list.add(rslt.getObject(i));
                }
                maps.add(list);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(rslt,psmt,conn);
        }
        return maps;
    }

    /**
     *
     */
    public static List<Map<String, Object>> queryListMap(String sql, Object... obj){
        Connection conn = getConn();
        PreparedStatement ps = null;
        List<Map<String, Object>> bigList = null;
        ResultSet set = null;
        try {
            //3.sql预编译
            ps = conn.prepareStatement(sql);
            //设置参数
            setParams(ps,obj);
            //4.执行sql
            set = ps.executeQuery();
            bigList = new ArrayList<>();
            while (set.next()){
                Map<String, Object> map = new HashMap<>();
                for (int i = 0; i < set.getMetaData().getColumnCount(); i++) {
                    map.put(set.getMetaData().getColumnName(i+1),set.getObject(i+1));
                }
                bigList.add(map);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            close(set,ps,conn);
        }
        return bigList;
    }

    /**
     * 增删改
     */
    public static int update(String sql,Object...obj){
        Connection conn = getConn();
        PreparedStatement psmt = null;
        try {
            psmt = conn.prepareStatement(sql);
            //传参
            setParams(psmt,obj);
            //执行
            int i = psmt.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(null,psmt,conn);
        }
        return -1;
    }

    /**
     * 关闭资源
     */
    public static void close(ResultSet rslt,PreparedStatement psmt,Connection conn){
        try {
            if(null != rslt){
                rslt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(null != psmt){
                psmt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(null != conn){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
